const db = require('@/db/db-connection')
const { multipleColumnSet } = require('@/utils/common.util')
const { newRandomId, dateFormat } = require('@/utils/common.util')

class SiteFolderModel {
  constructor () {
    this.tableName = 'site_folder'
  }

  /**
   * find user folders
   * @param { * } params
   */
  async find (params) {
    try {
      const { columnSet, values } = multipleColumnSet(params, ' AND ')

      const sql = `SELECT * FROM ${this.tableName} WHERE ${columnSet} ORDER BY sort DESC`

      const result = await db.query(sql, values)

      return result
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * create site_folder
   * @param { String } name folder name
   * @param { String } userId
   */
  async add ({ name, userId, isPrivate }) {
    try {
      const [id, createTime] = [newRandomId(), dateFormat(new Date())]

      const maxSort = await this.maxSort({ userId })

      const sql = `INSERT INTO ${this.tableName} (id, name, userId, sort, isPrivate, createTime) VALUES (?, ?, ?, ?, ?, ?)`

      await db.query(sql, [id, name, userId, maxSort + 1, isPrivate, createTime])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * update folder
   * @param {*} param
   */
  async update ({ name, isPrivate, id }) {
    try {
      const updateTime = dateFormat(new Date())

      const { columnSet, values } = multipleColumnSet({ name, isPrivate, updateTime })

      const sql = `UPDATE ${this.tableName} SET ${columnSet} WHERE id = ?`

      await db.query(sql, [...values, id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * find target folder
   * @param {*} params
   */
  async findOne (params) {
    try {
      const { columnSet, values } = multipleColumnSet(params, ' AND ')

      const sql = `SELECT * FROM ${this.tableName} WHERE ${columnSet}`

      const result = await db.query(sql, values)

      return result[0]
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
  * delete one folder
  * @param { String } id
  */
  async delete (id) {
    try {
      const sql = `DELETE FROM ${this.tableName} WHERE id = ?`

      await db.query(sql, [id])
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * find site_folder key sort max
   * @param {*} params
   */
  async maxSort (params) {
    try {
      const { columnSet, values } = multipleColumnSet(params, ' AND ')

      const sql = `SELECT MAX(sort) AS max FROM ${this.tableName} WHERE ${columnSet}`

      const result = await db.query(sql, [values])

      return result[0].max
    } catch (error) {
      throw new Error(error)
    }
  }

  /**
   * batch update site_folder sort key
   * @param {*} ids
   */
  async updateSort (ids, userId) {
    try {
      const max = ids.length

      let sql = ids.reduce((prev, cur, idx) => {
        prev += ` WHEN '${cur}' THEN ${max - idx}`
        return prev
      }, '')

      sql = `UPDATE ${this.tableName} SET sort = CASE id ${sql} END WHERE userId = ?`

      await db.query(sql, userId)
    } catch (error) {
      throw new Error(error)
    }
  }
}

module.exports = new SiteFolderModel()
